﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.JCI.Common.Utility;

namespace Microsoft.JCI.Common.Utility
{
    public class DalBase : DatabaseBase
    {
        public DalBase()
        {
        }

        public DalBase(string dbName)
            : base(dbName)
        {
        }

        #region public method

        public void Insert(object objEntity)
        {
            string tbName = objEntity.GetType().Name;
            object[] tbNameAtts = objEntity.GetType().GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }

            string sqlStr = "Insert Into {0} ({1}) Values ({2})";
            string fieldNames = "";
            string fieldValues = "";

            PropertyInfo[] Properties = objEntity.GetType().GetProperties();
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;

                fieldNames += property.Name + ",";

                fieldValues += "@" + property.Name + ",";
            }
            if (fieldNames != "") fieldNames = fieldNames.Trim(',');
            if (fieldValues != "") fieldValues = fieldValues.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldNames, fieldValues);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;

                if (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objEntity, null) == Guid.Empty)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }
                else if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)property.GetValue(objEntity, null)))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }

                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objEntity, null));
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public void Update(object objEntity)
        {
            Type objType = objEntity.GetType();
            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);
            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }

            string sqlStr = "Update {0} Set {1} Where {2}";
            string fieldSets = "";
            string fieldWheres = "";
            PropertyInfo[] Properties = objType.GetProperties();
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }
                fieldSets += property.Name + "=@" + property.Name + ",";
            }

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);
            if (fieldSets != "") fieldSets = fieldSets.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldSets, fieldWheres);
            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;

                if (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objEntity, null) == Guid.Empty)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }
                else if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)property.GetValue(objEntity, null)))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }

                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objEntity, null));
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public void InsertOrUpdate(object objEntity)
        {
            Type objType = objEntity.GetType();
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);
            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }

            string sqlStr = "IF NOT EXISTS(SELECT * FROM {0} WHERE {2}) INSERT INTO {0} ({3}) VALUES ({4}) ELSE UPDATE {0} SET {1} WHERE {2} ";
            string fieldSets = "";
            string fieldWheres = "";
            string fieldNames = "";
            string fieldValues = "";

            PropertyInfo[] properties = objType.GetProperties();
            foreach (PropertyInfo property in properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }
                fieldNames += property.Name + ",";
                fieldValues += "@" + property.Name + ",";
                fieldSets += property.Name + "=@" + property.Name + ",";
            }

            if (fieldNames != "") fieldNames = fieldNames.Trim(',');
            if (fieldValues != "") fieldValues = fieldValues.Trim(',');
            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);
            if (fieldSets != "") fieldSets = fieldSets.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldSets, fieldWheres, fieldNames, fieldValues);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objEntity, null) == DateTime.MinValue) continue;
                if (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objEntity, null) == Guid.Empty)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }
                else if (property.PropertyType == typeof(string) && string.IsNullOrEmpty((string)property.GetValue(objEntity, null)))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }
                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objEntity, null));
            }
            _db.ExecuteNonQuery(dbCommand);
        }

        public void Delete(object objEntity)
        {
            Type objType = objEntity.GetType();
            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);
            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }

            string sqlStr = "Delete from {0} Where {1}";
            string fieldWheres = "";
            PropertyInfo[] Properties = objType.GetProperties();
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }
            }

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            sqlStr = string.Format(sqlStr, tbName, fieldWheres);
            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objEntity, null));
                }
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public void Delete<T>(List<SqlDbParameter> parameters)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            //StringBuilder sqlStr = new StringBuilder("delete from {0} where 1=1 ");
            //string sqlFormat = sqlStr.AppendFormat(sqlStr.ToString(), tbName).ToString();

            StringBuilder sqlStr = new StringBuilder("delete from ");
            string sqlFormat = sqlStr.Append(tbName).Append(" where 1=1  {1}").ToString();

            string sql = SqlDbParameter.BuildSqlString(sqlFormat, parameters);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public void Delete<T>(string WhereClause)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "delete From {0} Where 1=1  {1}";
            sqlStr = string.Format(sqlStr, tbName, WhereClause);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            _db.ExecuteNonQuery(dbCommand);
        }

        public void LogicDelete(object objEntity)
        {
            Type objType = objEntity.GetType();
            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);
            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "update  {0} set Deleted=1 Where {1}";
            string fieldWheres = "";
            PropertyInfo[] Properties = objType.GetProperties();
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }
            }

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            sqlStr = string.Format(sqlStr, tbName, fieldWheres);
            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            foreach (PropertyInfo property in Properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objEntity, null));
                }
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public void LogicDelete<T>(List<SqlDbParameter> parameters)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            //StringBuilder sqlStr = new StringBuilder("delete from {0} where 1=1 ");
            //string sqlFormat = sqlStr.AppendFormat(sqlStr.ToString(), tbName).ToString();

            StringBuilder sqlStr = new StringBuilder("update ");
            string sqlFormat = sqlStr.Append(tbName).Append(" set deleted=1 where 1=1  {1}").ToString();

            string sql = SqlDbParameter.BuildSqlString(sqlFormat, parameters);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }

            _db.ExecuteNonQuery(dbCommand);
        }

        public T Get<T>(object primaryID)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "Select {1} From {0} Where {2}";
            string fieldWheres = "";
            string selectFields = "";

            PropertyInfo[] properties = objType.GetProperties();
            foreach (PropertyInfo property in properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                selectFields += property.Name + ",";
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }
            }

            if (selectFields != "") selectFields = selectFields.Trim(',');
            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            sqlStr = string.Format(sqlStr, tbName, selectFields, fieldWheres);
            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            foreach (PropertyInfo property in properties)
            {
                object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                if (efAttr != null && efAttr.Length > 0)
                {
                    continue;
                }
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), primaryID);
                }
            }
            using (IDataReader theReader = _db.ExecuteReader(dbCommand))
            {
                return GetFromReader<T>(theReader);
            }
        }

        public T Get<T>(string whereClause)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "Select top 1 * From {0} Where 1=1 {1}";
            sqlStr = string.Format(sqlStr, tbName, whereClause);
          
            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            using (IDataReader theReader = _db.ExecuteReader(dbCommand))
            {
                return GetFromReader<T>(theReader);
            }
        }

        public T Get<T>(List<SqlDbParameter> parameters)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            StringBuilder sqlStr = new StringBuilder("select top 1 * from  ");

            string sqlFormat = sqlStr.Append(tbName).Append(" where 1=1 {1}").ToString();

            string sql = SqlDbParameter.BuildSqlString(sqlFormat, parameters);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }
            using (IDataReader theReader = this._db.ExecuteReader(dbCommand))
            {
                return GetFromReader<T>(theReader);
            }
        }

        public List<T> GetAll<T>(List<SqlDbParameter> parameters)
        {
            return GetAll<T>(parameters, string.Empty);
        }

        public List<T> GetAll<T>(List<SqlDbParameter> parameters, string OrderBy)
        {
            List<T> objList = new List<T>();

            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "select  {0} * from " + tbName + " where 1=1 {1} " + OrderBy;
            string sql = SqlDbParameter.BuildSqlString(sqlStr, parameters);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }
            using (IDataReader theReader = this._db.ExecuteReader(dbCommand))
            {
                return GetListFromReader<T>(theReader);
            }
        }

        public List<T> GetAll<T>(string whereClause)
        {
            return GetAll<T>(whereClause, string.Empty);
        }

        public List<T> GetAll<T>(string whereClause, string OrderBY)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }

            string sqlStr = "Select * From {0} Where 1=1 {1} " + OrderBY;

            sqlStr = string.Format(sqlStr, tbName, whereClause);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);
            using (IDataReader theReader = _db.ExecuteReader(dbCommand))
            {
                return GetListFromReader<T>(theReader);
            }
        }
        public List<T> GetAll<T>(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {

            List<T> objList = new List<T>();

            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            StringBuilder sqlSb = new StringBuilder("select  * from ");
            sqlSb.Append(tbName);
            sqlSb.Append(" where 1=1  {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER( {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            _db.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            _db.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            IDataReader reader = null;
            using (reader = _db.ExecuteReader(dbCommand))
            {
                objList = GetListFromReader<T>(reader);
                if (reader.NextResult() && reader.Read())
                    RecordCount = reader.GetInt32(0);
                else
                    RecordCount = 0;
            }
            return objList;
        }

        public List<T> GetAll<T>(List<SqlDbParameter> parameters, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            List<T> objList = new List<T>();

            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            StringBuilder sqlStr = new StringBuilder("select {0} * from ");
            sqlStr.Append(tbName);

            sqlStr.Append(" where 1=1 {1}");

            string sql = SqlDbParameter.BuildSqlString(sqlStr.ToString(), parameters);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sql.ToString(), sql.ToString(), OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            _db.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            _db.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);
            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }

            IDataReader reader = null;
            using (reader = _db.ExecuteReader(dbCommand))
            {
                objList = GetListFromReader<T>(reader);
                if (reader.NextResult() && reader.Read())
                    RecordCount = reader.GetInt32(0);
                else
                    RecordCount = 0;
            }
            return objList;
        }

        public DataTable GetAll(String sql, List<SqlDbParameter> parameters, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            DataTable dt = new DataTable();
            sql = SqlDbParameter.BuildSqlString(sql, parameters);
            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sql, sql, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }
            dt = this.CurrentDatabase.ExecuteDataSet(dbCommand).Tables[0];

            CmdText = @"SELECT  COUNT(*) FROM  ({0}) as B";
            CmdText = string.Format(CmdText, sql.ToString());
            dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);

            foreach (SqlDbParameter parameter in parameters)
            {
                parameter.SetDbCommond(dbCommand);
            }

            RecordCount = (int)this.CurrentDatabase.ExecuteScalar(dbCommand);
            return dt;
        }

        public int Count<T>(string WhereClause)
        {
            Type objType = typeof(T);
            string tbName = objType.Name;
            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];
                tbName = tableName.Name;
            }
            string sqlStr = "Select count(1) From {0} Where 1=1 {1}";
            sqlStr = string.Format(sqlStr, tbName, WhereClause);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            return (int)_db.ExecuteScalar(dbCommand);
        }
        #endregion

        #region protected method
        /// <summary>
        /// convert to BType from System.Type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        protected DbType CovertToDBType(System.Type type)
        {
            String dbTypeName = String.Empty;
            if (type.FullName.Contains("Foicp.ProjectName.Common.Enumerator"))
            {
                dbTypeName = "Int32";
            }
            else
            {
                int keyCount = DBTypeConversionKey.GetLength(0);
                for (int i = 0; i < keyCount; i++)
                {
                    if (DBTypeConversionKey[i, 1].Equals(type.FullName))
                    {
                        dbTypeName = DBTypeConversionKey[i, 0];
                        break;
                    }

                }
            }
            if (dbTypeName == String.Empty)
            {
                dbTypeName = "String";
            }
            //dbTypeName = "Variant";
            return (DbType)Enum.Parse(typeof(DbType), dbTypeName);

        }

        protected T GetFromReader<T>(IDataReader theReader)
        {
            Type objType = typeof(T);
            object objEntity = Activator.CreateInstance(objType);
            PropertyInfo[] properties = objType.GetProperties();
            if (theReader.Read())
            {
                foreach (PropertyInfo propertyInfo in properties)
                {
                    object[] efAttr = propertyInfo.GetCustomAttributes(typeof(ExtendField), false);
                    if (efAttr != null && efAttr.Length > 0)
                    {
                        continue;
                    }
                    if (theReader[propertyInfo.Name] == null || theReader[propertyInfo.Name] == DBNull.Value) continue;

                    propertyInfo.SetValue(objEntity, theReader[propertyInfo.Name], null);
                }
            }
            else return default(T);
            return (T)objEntity;
        }


        protected List<T> GetListFromReader<T>(IDataReader theReader)
        {
            List<T> objList = new List<T>();
            object objEntity = null;

            Type objType = typeof(T);
            PropertyInfo[] properties = objType.GetProperties();

            while (theReader.Read())
            {
                objEntity = Activator.CreateInstance(objType);

                foreach (PropertyInfo property in properties)
                {
                    object[] efAttr = property.GetCustomAttributes(typeof(ExtendField), false);
                    if (efAttr != null && efAttr.Length > 0)
                    {
                        continue;
                    }
                    if (theReader[property.Name] == null || theReader[property.Name] == DBNull.Value) continue;

                    property.SetValue(objEntity, theReader[property.Name], null);
                }

                objList.Add((T)objEntity);
            }


            return objList;
        }
        #endregion

        #region private method
        /// <summary>
        ///  the mapping of DbType and System.Type
        /// </summary>
        private static String[,] DBTypeConversionKey = new String[,] 
            {
             {"Binary", "System.Byte[]"},
             {"Byte", "System.Byte"},
             {"Boolean", "System.Boolean"},
             {"DateTime", "System.DateTime"},
             {"Decimal", "System.Decimal"},
             {"Double", "System.Double"},
             {"Guid", "System.Guid"},
             {"Int16", "System.Int16"},
             {"Int32", "System.Int32"},
             {"Int64", "System.Int64"},
             {"Object", "System.Object"},
             {"SByte", "System.SByte"},
             {"Single", "System.Single"},
             {"String", "System.String"},
             {"UInt16", "System.UInt16"},
             {"UInt32", "System.UInt32"},
             {"UInt64", "System.UInt64"},
             {"Xml", "System.Xml"}
            };

        #endregion
    }
}
